﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel;
using ExcelHandler;
using ExcelLibrary.SpreadSheet;
using QiHe.CodeLib;

namespace ExcelLibrary.FormTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private readonly string filePath = "Excel报表示例.xls";

        private  void ExcelLibraryHandler()
        {
               //Stream fileStream = File.OpenRead("Excel报表示例.xlsx");
                var book = Workbook.Open(filePath);
                var sheet = book.Worksheets[0];
                var rowCount = sheet.Cells.LastRowIndex + 1;
                var colCount = sheet.Cells.LastColIndex + 1;

                dgvCells.Dock = DockStyle.Fill;
                dgvCells.RowCount = rowCount;
                dgvCells.ColumnCount = colCount;

                // tranverse cells
                //foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
                //{
                //    dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
                    
                //    if (cell.Right.Style.BackColor != Color.White)
                //    {
                //        dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
                //    }
                //}

            DataTable dt = new DataTable();
            for (int i = 0; i < colCount; i++)
            {
                dt.Columns.Add("Column"+i.ToString());
            }

                for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                {
                    Row row = sheet.Cells.GetRow(rowIndex);
                    DataRow dr = dt.NewRow();
                    for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
                    {
                        Cell cell = row.GetCell(colIndex);

                        //if (cell.Value == null)
                        //{
                        //    if (colIndex == 0 && rowIndex > 0)
                        //    {
                        //        dgvCells[colIndex, rowIndex].Value = sheet.Cells.GetRow(rowIndex - 1).GetCell(0).Value;
                        //        continue; 
                        //    }

                        //    if (colIndex > 0)
                        //    {
                        //        dgvCells[colIndex, rowIndex].Value = dgvCells[colIndex-1, rowIndex].Value;
                        //        continue;
                        //    }
                        //}
      
                       // dgvCells[colIndex, rowIndex].Value = cell.Value;
                        dr[colIndex] = cell.Value==null?string.Empty:cell.Value.ToString();
                    }

                    dt.Rows.Add(dr);
                }
        }

        private  void ExcelHandlerMethod()
        {
            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
           //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row
            //excelReader.IsFirstRowAsColumnNames = false;
            //DataSet result = excelReader.AsDataSet();

            //5. Data Reader methods
            //while (excelReader.Read())
            //{
            //    //excelReader.GetInt32(0);
            //}

            dgvCells.DataSource = result.Tables[0];
            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
        }

        private  void SelfHandler()
        {
            OleDbConnection objConn = null;
            DataSet data = new DataSet();
            try
            {
                string root = AppDomain.CurrentDomain.BaseDirectory+filePath;
               // string strConn = @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + root + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";//IMEX=1为只读
                //"Provider=Microsoft.Jet.OleDb.4.0;data source=c:\待发工资.xlsx;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";
              string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +root + ";Extended Properties=Excel 8.0;";
                 
                objConn = new OleDbConnection(strConn);
                objConn.Open();
                //System .Data . DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //int SheetIndex = 0;
                //string tableName = schemaTable.Rows[SheetIndex][2].ToString().Trim();

                string strSql = "Select   *   From   [Sheet1$]";
                OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                OleDbDataAdapter sqlada = new OleDbDataAdapter();
                sqlada.SelectCommand = objCmd;
                sqlada.Fill(data);
                dgvCells.DataSource = data.Tables[0];
                objConn.Close();
            }
            catch (Exception ex)
            {
                objConn.Close();
                MessageBox.Show(ex.Message);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                var excelExtract = new ExcelExtract(this.filePath);
                DataTable dt = excelExtract.ExtractData();
                this.dgvCells.DataSource = dt;
            }
            catch (Exception exception)
            {

                MessageBox.Show(exception.Message);
            }
     
        }

        private DataTable GetExcelData(string filePath)
        {
            return new DataTable();
        }
    }
}
